package com.pro.addresslist.sqllite;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.pro.addresslist.dal.model.ContactInfo;
import com.pro.addresslist.dal.model.ContactInfoBySqlite;
import com.pro.addresslist.ui.view.CharacterParser;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ContactInfoServise {
	 Context      context = null;  
	    DBOpenHelper dbOpenHelper = null;  
	    private final int State_Initialization = 0; //初始化
	    private final int State_Add = 1; //新增
	    private final int State_Update = 2; //修改
	    private final int State_Del= 3; //删除

public ContactInfoServise(Context context) {
	this.context = context;  
	dbOpenHelper = new DBOpenHelper( context );  
	// TODO Auto-generated constructor stub
}

/**
 * 查询联系人（单人）
 */
public  List<ContactInfoBySqlite> SelectContactByID(Integer contactID){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String sql="select  distinct  *  from  ContactInfo where id="+contactID+" and  State != 3";
	Cursor cursor = db.rawQuery(sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
   return list;

}

/**
 * 查询id（朋友）
 */
public  String SelectIdByContactId(String contactID){
	
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String sql="select * from ContactInfo where ContactID='"+contactID+"' and State != 3";
	Cursor cursor = db.rawQuery(sql, null);
	String result="";
	if(cursor.moveToNext()){
	result = cursor.getInt(cursor.getColumnIndex("id"))+"";
	}
	cursor.close();	
	return result;
}

/**
 * 修改联系人地域
 * @return 
 */
public boolean UpdataContactBydy(Integer contactID,String dy){
	try{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
		String sql="update ContactInfo set dy=? where id=?";
		Object[] param={dy,contactID};
	    db.execSQL(sql, param);  
	    return true;
	    }
	catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}

}

/**
 *修改联系人归口
 * @return 
 */
public  boolean UpdataContactBygk(Integer id,String gk){
	try{
	SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
	String sql="update ContactInfo set gk=? where id=?";
	Object[] param={gk,id};
    db.execSQL(sql, param);  
    return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContactBygk"+ex.getMessage());
		return false;
	}
	
}
/**
 *修改联系人状态
 * @return 
 */
public  boolean UpdataContactStateByID(Integer ID,int state){
	try{
	SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
	String sql="update ContactInfo set State=? where id=?";
	Object[] param={state,ID};
    db.execSQL(sql, param);  
    return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContactBygk"+ex.getMessage());
		return false;
	}
	
}


/**
 * 汉字转换成拼音的类
 */
private CharacterParser characterParser;

/**
 * 新建联系人,State未韵定
 * @author leekh
 */
public Boolean AddContact(ContactInfoBySqlite coninfo){
	try{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		
		String sql="insert into ContactInfo(ContactID,NAMEPY,HeadImage,Name,Sex,Nationality,Unit,Department,gk,dy,Post,Birthday,Interest,Food,Mobile,DepartTell,FamilyTell,Address,DepartAddress, OtherTell, Depict, State) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		// 实例化汉字转拼音类
		characterParser = CharacterParser.getInstance();
		String pinyin = characterParser.getSelling(coninfo.get_Name());	
		String sortString = pinyin.substring(0, 1).toUpperCase();	
		// 正则表达式，判断首字母是否是英文字母
					if (!sortString.matches("[A-Z]")) {
						sortString="#";
					} 
		//加入数据最后一个状态置为1.表示初始状态。
		Object[] val={coninfo.get_ContactID(),sortString,coninfo.get_HeadImage(),coninfo.get_Name(),coninfo.get_Sex(),coninfo.get_Nationality(),coninfo.get_Unit(),coninfo.get_Department(),coninfo.get_gk(),coninfo.get_dy(),coninfo.get_Post(),coninfo.get_Birthday(),coninfo.get_Interest(),coninfo.get_Food(),coninfo.get_Mobile(),coninfo.get_DepartTell(),coninfo.get_FamilyTell(),coninfo.get_Address(),coninfo.get_DepartAddress(),coninfo.get_OtherTell(),coninfo.get_Depict(),State_Add};
		db.execSQL(sql,val);
		return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"AddContact"+ex.getMessage());
		return false;
	}
	}
/**
 * 增加联系人从远程数据库
 * @author leekh
 */
public Boolean AddContactdatabase(ContactInfo coninfo){
	try{
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		
		String sql="insert into ContactInfo(ContactID,NAMEPY,HeadImage,Name,Sex,Nationality,Unit,Department,gk,dy,Post,Birthday,Interest,Food,Mobile,DepartTell,FamilyTell,Address,DepartAddress, OtherTell, Depict, State) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		// 实例化汉字转拼音类
		characterParser = CharacterParser.getInstance();
		String pinyin = characterParser.getSelling(coninfo.get_Name());	
		String sortString = pinyin.substring(0, 1).toUpperCase();	
		// 正则表达式，判断首字母是否是英文字母
					if (!sortString.matches("[A-Z]")) {
						sortString="#";
					} 
		//加入数据最后一个状态置为0.表示初始状态。
		Object[] val={coninfo.get_ContactID(),sortString,coninfo.get_HeadImage(),coninfo.get_Name(),coninfo.get_Sex(),coninfo.get_Nationality(),coninfo.get_Unit(),coninfo.get_Department(),coninfo.get_gk(),coninfo.get_dy(),coninfo.get_Post(),coninfo.get_Birthday(),coninfo.get_Interest(),coninfo.get_Food(),coninfo.get_Mobile(),coninfo.get_DepartTell(),coninfo.get_FamilyTell(),coninfo.get_Address(),coninfo.get_DepartAddress(),coninfo.get_OtherTell(),coninfo.get_Depict(),State_Initialization};
		db.execSQL(sql,val);
		return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"AddContact"+ex.getMessage());
		return false;
	}
	}
/**
 * 编辑联系人,
 */
public Boolean UpdataContact(ContactInfoBySqlite coninfo){
	try{
		//这里需要判断联系人状态是否为增加，如果为增加，联系人状态不变，否则变为修改	
		
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql="update ContactInfo set NAMEPY = ?,HeadImage = ?,Name = ?,Sex = ?,Nationality = ?,Unit = ?,Department = ?,gk = ?,dy = ?,Post = ?,Birthday = ?,Interest = ?,Food = ?,Mobile = ?,DepartTell = ?,FamilyTell = ?,Address = ?,DepartAddress = ?, OtherTell = ?, Depict = ?, State = ? where id = ?";
		int res=SelectStateByid(coninfo.get_id());
		int usestate = 0;
		if(res == 1){
			usestate = State_Add;//是新增标识则状态不变
		}
		else{
			usestate = State_Update;//否则状态未更改
		}
		characterParser = CharacterParser.getInstance();
		String pinyin = characterParser.getSelling(coninfo.get_Name());	
		String sortString = pinyin.substring(0, 1).toUpperCase();
		
		Object[] val={sortString,coninfo.get_HeadImage(),coninfo.get_Name(),coninfo.get_Sex(),coninfo.get_Nationality(),coninfo.get_Unit(),coninfo.get_Department(),coninfo.get_gk(),coninfo.get_dy(),coninfo.get_Post(),coninfo.get_Birthday(),coninfo.get_Interest(),coninfo.get_Food(),coninfo.get_Mobile(),coninfo.get_DepartTell(),coninfo.get_FamilyTell(),coninfo.get_Address(),coninfo.get_DepartAddress(),coninfo.get_OtherTell(),coninfo.get_Depict(),usestate,coninfo.get_id()};	
		db.execSQL(sql, val);
		return true;
	}catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}
}

/**
 * 删除联系人,本地sql标识ID，不是联系人id。仅修改状态
 */
public Boolean DelContactByID(Integer id){
try{
		
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql="update ContactInfo set State = ? where id = ?";
		
		Object[] val={State_Del,id};
		db.execSQL(sql, val);
		return true;
	}catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}
}

/**
 * 删除联系人,直接从数据库删除
 */
public Boolean DelContactByIDTrue(Integer id){
try{
		
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql="delete from ContactInfo where id = ?";	
		Object[] val={id};
		db.execSQL(sql, val);
		return true;
	}catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}
}


/**
 * 计算联系人数量（归口）
 */
public int SelectContactNumBygk(String gk){
	
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	Cursor cursor = db.rawQuery("select count(*) from ContactInfo where not gk is null and gk != '' and gk like '%"+gk+"%'", null);
	cursor.moveToFirst();
	int result = cursor.getInt(0);
	cursor.close();	
	return result;
}
/**
 * 计算联系人数量（地域）
 */
public int SelectContactNumBydy(String dy){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	Cursor cursor = db.rawQuery("select count(*) from ContactInfo where not gk is null and dy != '' and dy like '%"+dy+"%'", null);
	cursor.moveToFirst();
	int result = cursor.getInt(0);
	cursor.close();	
	return result;
}

/**
 * 查询联系人生日(按未来一周的查)
 */
public List<ContactInfoBySqlite> SelectContactBySR(){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	//时间划定
	Date d = new Date();
	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	String nowdate = df.format(d);
	String sevendate = df.format(new Date(d.getTime() + 7 * 24 * 60 * 60 * 1000));
	String sql = "select * from ContactInfo where State = 0 and Birthday between '"+nowdate+"' and '"+sevendate+"'";
	Cursor cursor = db.rawQuery(sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}

/**
 * 通过id查询状态
 * @param id
 * @return
 */
public int SelectStateByid(Integer id){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String sql = "select State from ContactInfo where id ="+id+"";
	Cursor cursor = db.rawQuery(sql, null);
	cursor.moveToFirst();
	int result = cursor.getInt(cursor.getColumnIndex("State"));
	cursor.close();	
	return result;
	
}
/**
 * 查询联系人（地域，模糊查询）
 */
public List<ContactInfoBySqlite> SelectContactBydy(String dy){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3 and dy like '%"+dy+"%'";

	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}
/**
 * 查询联系人（归口，模糊查询）
 */
public List<ContactInfoBySqlite> SelectContactBygk(String gk){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3 and gk like '%"+gk+"%'";

	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}
/**
 * 查询联系人（姓名，模糊查询）
 */
public List<ContactInfoBySqlite> SelectContactByName(String Name){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3 and Name like '%"+Name+"%'";

	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}
/**
 * 查询联系人（所有）
 */
public List<ContactInfoBySqlite> SelectContactByAll(){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3 ";

	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}
/**
 * 查询联系人（过滤朋友）
 */
public List<ContactInfoBySqlite> SelectContactByFriend(List<String> ContactIDs){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3";
	for(String conID:ContactIDs){
		_Sql+=" and ContactID!='"+conID+"'";
	}
	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}

/***
 * 查询联系人（未分组的，也就是地域和归口为空）
 */
public List<ContactInfoBySqlite> SelectContactByNoGroup(){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	String _Sql = "select * from ContactInfo where State != 3 and  (gk is NULL or dy is NULL  or gk ='' or dy='')";

	Cursor cursor = db.rawQuery(_Sql, null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);
	return list;
}
/**
 * 根据状态查询
 * @param state
 * @return
 */
public List<ContactInfoBySqlite> SelectCountByState(Integer state){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	Cursor cursor = db.rawQuery("select * from ContactInfo where State = "+state+"", null);
	List<ContactInfoBySqlite> list = DBwrite(cursor);	
	return list;
}

private List<ContactInfoBySqlite> DBwrite(Cursor cursor) {
	List<ContactInfoBySqlite> list=new ArrayList<ContactInfoBySqlite>();
	while(cursor.moveToNext()){
		ContactInfoBySqlite coninfo = new ContactInfoBySqlite();
		coninfo.set_id(cursor.getInt(cursor.getColumnIndex("id")));
		coninfo.set_State(cursor.getInt(cursor.getColumnIndex("State")));
		coninfo.set_NAMEPY(cursor.getString(cursor.getColumnIndex("NAMEPY")));
		coninfo.set_Address(cursor.getString(cursor.getColumnIndex("Address")));
		coninfo.set_Birthday(cursor.getString(cursor.getColumnIndex("Birthday")));
		coninfo.set_ContactID(cursor.getString(cursor.getColumnIndex("ContactID")));
		coninfo.set_DepartAddress(cursor.getString(cursor.getColumnIndex("DepartAddress")));
		coninfo.set_Department(cursor.getString(cursor.getColumnIndex("Department")));
		coninfo.set_DepartTell(cursor.getString(cursor.getColumnIndex("DepartTell")));
		coninfo.set_Depict(cursor.getString(cursor.getColumnIndex("Depict")));
		coninfo.set_dy(cursor.getString(cursor.getColumnIndex("dy")));
		coninfo.set_FamilyTell(cursor.getString(cursor.getColumnIndex("FamilyTell")));
		coninfo.set_Food(cursor.getString(cursor.getColumnIndex("Food")));
		coninfo.set_gk(cursor.getString(cursor.getColumnIndex("gk")));
		coninfo.set_HeadImage(cursor.getString(cursor.getColumnIndex("HeadImage")));
		coninfo.set_Interest(cursor.getString(cursor.getColumnIndex("Interest")));
		coninfo.set_Mobile(cursor.getString(cursor.getColumnIndex("Mobile")));
		coninfo.set_Name(cursor.getString(cursor.getColumnIndex("Name")));
		coninfo.set_Nationality(cursor.getString(cursor.getColumnIndex("Nationality")));
		coninfo.set_OtherTell(cursor.getString(cursor.getColumnIndex("OtherTell")));
		coninfo.set_Post(cursor.getString(cursor.getColumnIndex("Post")));
		coninfo.set_Sex(cursor.getInt(cursor.getColumnIndex("Sex")));
		coninfo.set_Unit(cursor.getString(cursor.getColumnIndex("Unit")));
		list.add(coninfo);
	}
	cursor.close();
	return list;
}

/**
 * 查询状态不为0的数量
 * @param state
 * @return
 */
public int SelectCountByState(){
	SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
	Cursor cursor = db.rawQuery("select count(*) from ContactInfo where State != 0 ", null);
	cursor.moveToFirst();
	int result = cursor.getInt(0);
	cursor.close();	
	return result;
}
/**
 * 删除所有表数据,直接从数据库删除
 */
public Boolean DelContactAll(){
try{
		
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql="delete from ContactInfo";	
		db.execSQL(sql);
		return true;
	}catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}
}
/**
 *修改联系人状态
 * @return 
 */
public  boolean UpdataContactStateByConID(String ID,int state){
	try{
	SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
	String sql="update ContactInfo set State=? where ContactID=?";
	Object[] param={state,ID};
    db.execSQL(sql, param);  
    return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContactBygk"+ex.getMessage());
		return false;
	}
	
}
/**
 *修改联系人编号
 * @return 
 */
public  boolean UpdataContactIDByConID(String newID,String oldID){
	try{
	SQLiteDatabase db = dbOpenHelper.getWritableDatabase();  
	String sql="update ContactInfo set ContactID=? where ContactID=?";
	Object[] param={newID,oldID};
    db.execSQL(sql, param);  
    return true;
	}
	catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContactBygk"+ex.getMessage());
		return false;
	}
	
}
/**
 * 删除联系人,直接从数据库删除
 */
public Boolean DelContactByConIDTrue(String id){
try{
		
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		String sql="delete from ContactInfo where ContactID = ?";	
		Object[] val={id};
		db.execSQL(sql, val);
		return true;
	}catch(Exception ex){
		Log.i("sql", "error-->"+"UpdataContact"+ex.getMessage());
		return false;
	}
}

}